BEGIN TRANSACTION
GO
/****** Object:  Table [dbo].[Invoice_GRN]    Script Date: 06/20/2023 12:47:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Invoice_GRN](
	[Inv_GRNID] [bigint] IDENTITY(1,1) NOT NULL,
	[Inv_GRNDate] [datetime] NOT NULL,
	[Inv_GRNVNo] [varchar](16) NOT NULL,
	[SessionID] [smallint] NOT NULL,
	[Remarks] [varchar](150) NULL,
	[ReadOnly] [bit] NOT NULL CONSTRAINT [DF_Invoice_GRN_ReadOnly]  DEFAULT ((0)),
	[LoginID] [int] NOT NULL CONSTRAINT [DF_Invoice_GRN_LoginID]  DEFAULT ((1)),
	[HostName] [varchar](50) NULL CONSTRAINT [DF_Invoice_GRN_HostName]  DEFAULT (host_name()),
	[EntryDateTime] [datetime] NULL CONSTRAINT [DF_Invoice_GRN_EntryDateTime]  DEFAULT (getdate()),
	[ModifyID] [int] NULL,
	[ModifyHostName] [varchar](50) NULL,
	[ModifyDateTime] [datetime] NULL,
	[BLID] [bigint] NULL,
	[BLNo] [varchar](50) NULL,
	[SumQTY] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_GRN_SumQTY]  DEFAULT ((0)),
	[ContainerNo] [varchar](200) NULL,
	[NoOfContainers] [decimal](11, 2) NOT NULL CONSTRAINT [DF_Invoice_GRN_NoOfContainers]  DEFAULT ((0)),
 CONSTRAINT [PK_Invoice_GRN] PRIMARY KEY CLUSTERED 
(
	[Inv_GRNID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

GO
COMMIT

-------------------------------

BEGIN TRANSACTION
GO

/****** Object:  Table [dbo].[Invoice_GRNitem]    Script Date: 06/20/2023 12:47:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Invoice_GRNitem](
	[Inv_GRNitemID] [bigint] IDENTITY(1,1) NOT NULL,
	[Inv_GRNID] [bigint] NOT NULL,
	[Dis_Date] [datetime] NOT NULL,
	[PartyID] [int] NULL,
	[ItemID] [smallint] NULL,
	[VarietyID] [smallint] NULL,
	[Qty] [numeric](15, 4) NOT NULL CONSTRAINT [DF_Invoice_GRNitem_Qty]  DEFAULT ((0)),
	[Type] [tinyint] NOT NULL CONSTRAINT [DF_Invoice_GRNitem_Type]  DEFAULT ((0)),
	[ContainerNo] [varchar](20) NULL,
	[DRemarks] [varchar](100) NULL,
 CONSTRAINT [PK_Invoice_GRNitem] PRIMARY KEY CLUSTERED 
(
	[Inv_GRNitemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

GO
COMMIT

-------------------------------

BEGIN TRANSACTION
GO

ALTER VIEW [dbo].[vw_BLNo_Bank_GDN]
AS
SELECT     dbo.Invoice_Bank.Inv_BankID, dbo.Invoice_Bank.Inv_BankDate, dbo.Invoice_Bank.Inv_BankVNo AS V_No, dbo.Invoice_Bank.BLNo AS BL_No, dbo.Invoice_Bank.BLDate AS BL_Date, 
                      dbo.Invoice_Bank.ConsigneeID AS Party_ID, dbo.Accounts.AccountTitle AS Party_Name, dbo.Invoice_Bank.BLNo + ' ' + dbo.Invoice_BankItem.ContainerNo + ' ' + dbo.Accounts.AccountTitle AS Search,
                       dbo.Invoice_BankItem.ItemID, dbo.Items.ItemName, dbo.Invoice_BankItem.VarietyID, dbo.Varietys.VarietyName, dbo.Invoice_BankItem.LQTY, dbo.Invoice_BankItem.ContainerNo AS Container_No, 
                      dbo.Invoice_Bank.Remarks
FROM         dbo.Invoice_Bank INNER JOIN
                      dbo.Accounts ON dbo.Invoice_Bank.ConsigneeID = dbo.Accounts.AccountNo INNER JOIN
                      dbo.Invoice_BankItem ON dbo.Invoice_Bank.Inv_BankID = dbo.Invoice_BankItem.inv_BankID LEFT OUTER JOIN
                      dbo.Varietys ON dbo.Invoice_BankItem.VarietyID = dbo.Varietys.VarietyID LEFT OUTER JOIN
                      dbo.Items ON dbo.Invoice_BankItem.ItemID = dbo.Items.ItemID
WHERE     (dbo.Invoice_Bank.Inv_BankVNo LIKE 'BI-%') AND (dbo.Invoice_Bank.BLNo NOT IN
                          (SELECT DISTINCT BLNo
                            FROM          dbo.Invoice_GDN))
UNION ALL
SELECT     dbo.Invoice_import.Inv_importID, dbo.Invoice_import.Inv_importDate, dbo.Invoice_import.Inv_importVNo AS V_No, dbo.Invoice_import.BLNo AS BL_No, dbo.Invoice_import.BLDate AS BL_Date, 
                      dbo.Invoice_import.ConsigneeID AS Party_ID, Accounts_1.AccountTitle AS Party_Name, dbo.Invoice_import.BLNo + ' ' + dbo.Invoice_importItem.ContainerNo + ' ' + Accounts_1.AccountTitle AS Search,
                       dbo.Invoice_importItem.ItemID, Items_1.ItemName, dbo.Invoice_importItem.VarietyID, Varietys_1.VarietyName, dbo.Invoice_importItem.LQTY, dbo.Invoice_importItem.ContainerNo AS Container_No, 
                      dbo.Invoice_import.Remarks
FROM         dbo.Invoice_import INNER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.Invoice_import.ConsigneeID = Accounts_1.AccountNo INNER JOIN
                      dbo.Invoice_importItem ON dbo.Invoice_import.Inv_importID = dbo.Invoice_importItem.inv_importID LEFT OUTER JOIN
                      dbo.Varietys AS Varietys_1 ON dbo.Invoice_importItem.VarietyID = Varietys_1.VarietyID LEFT OUTER JOIN
                      dbo.Items AS Items_1 ON dbo.Invoice_importItem.ItemID = Items_1.ItemID
WHERE     (dbo.Invoice_import.Inv_importVNo LIKE 'IM-%') AND (dbo.Invoice_import.BLNo NOT IN
                          (SELECT DISTINCT BLNo
                            FROM          dbo.Invoice_GRN))

GO
COMMIT

-------------------------------

BEGIN TRANSACTION
GO

ALTER VIEW [dbo].[vw_container]
AS
SELECT     dbo.Invoice_GDN.Inv_GDNID, dbo.Invoice_GDN.Inv_GDNDate, dbo.Invoice_GDN.Inv_GDNVNo, dbo.Invoice_GDN.BLNo, dbo.Invoice_GDNitem.Dis_Date, dbo.Invoice_GDNitem.PartyID, 
                      dbo.Accounts.AccountTitle AS PartyName, dbo.Invoice_GDNitem.ItemID, dbo.vw_Items.ItemName, dbo.Invoice_GDNitem.VarietyID, dbo.vw_Items.VarietyName, dbo.Invoice_GDNitem.Qty, 
                      dbo.Invoice_GDNitem.ContainerNo, dbo.Invoice_GDNitem.DRemarks
FROM         dbo.Invoice_GDN INNER JOIN
                      dbo.Invoice_GDNitem ON dbo.Invoice_GDN.Inv_GDNID = dbo.Invoice_GDNitem.Inv_GDNID LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.Invoice_GDNitem.VarietyID = dbo.vw_Items.VarietyID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.Invoice_GDNitem.PartyID = dbo.Accounts.AccountNo
UNION ALL
SELECT     dbo.Invoice_GRN.Inv_GRNID, dbo.Invoice_GRN.Inv_GRNDate, dbo.Invoice_GRN.Inv_GRNVNo, dbo.Invoice_GRN.BLNo, dbo.Invoice_GRNItem.Dis_Date, dbo.Invoice_GRNItem.PartyID, 
                      Accounts_1.AccountTitle AS PartyName, dbo.Invoice_GRNItem.ItemID, vw_Items_1.ItemName, dbo.Invoice_GRNItem.VarietyID, vw_Items_1.VarietyName, dbo.Invoice_GRNItem.Qty, 
                      dbo.Invoice_GRNItem.ContainerNo, dbo.Invoice_GRNItem.DRemarks
FROM         dbo.Invoice_GRN INNER JOIN
                      dbo.Invoice_GRNItem ON dbo.Invoice_GRN.Inv_GRNID = dbo.Invoice_GRNItem.Inv_GRNID LEFT OUTER JOIN
                      dbo.vw_Items AS vw_Items_1 ON dbo.Invoice_GRNItem.VarietyID = vw_Items_1.VarietyID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.Invoice_GRNItem.PartyID = Accounts_1.AccountNo

GO
COMMIT